/*
 * By BEST PLM Team.
 *
 * Author: quhy1
 * Date: 2014-11-17
 */
package com.webridge.plm.util;

import java.io.Serializable;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import wt.log4j.LogR;
import wt.method.MethodContext;
import wt.method.RemoteAccess;
import wt.method.RemoteMethodServer;
import wt.pom.WTConnection;
import wt.util.WTException;
import wt.util.WTProperties;


import oracle.sql.ArrayDescriptor;

/**
 * 
 * @author Quhongye
 * 
 */
public class DBOperationUtil implements RemoteAccess, Serializable {
    private static final long serialVersionUID = 1L;
    private static final Logger LOGGER = LogR.getLogger(DBOperationUtil.class.getName());
    private static final String SQLSERVER_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

    private static final String PROXOOL_ALIAS = "proxool.alias";
    private static final String PROXOOL_MAXIMUMCONNECTIONCOUNT = "proxool.maximum-connection-count";
    private static final String PROXOOL_HOUSEKEEPINGTESTSQL = "proxool.house-keeping-test-sql";
    private static final String PROXOOL_MINIMUMCONNECTIONCOUNT = "proxool.minimum-connection-count";
    private static final String PROXOOL_SIMULTANEOUSBUILDTHROTTLE = "proxool.simultaneous-build-throttle";
    private static final String PROXOOL_MAXIMUMCONNECTIONLIFETIME = "proxool.maximum-connection-lifetime";
    private static final String PROXOOL_MAXIMUMACTIVETIME = "proxool.proxool.maximum-active-time";

    public static WTConnection getWTConnection() throws WTException {

        WTConnection wtConn = null;

        try {
            MethodContext methodcontext = getMethodContext();
            wtConn = (WTConnection) methodcontext.getConnection();
        } catch (Exception e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        }

        return wtConn;
    }

    /**
     * query from database for one column return.
     * 
     * @param sql
     * @param parameters
     * @return
     * @throws WTException
     */
    public static List<String> commonQueryAll(String sql, String[] parameters) throws WTException {
        List<String> list = new ArrayList<String>();

        WTConnection wtconnection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            MethodContext methodcontext = getMethodContext();
            if (RemoteMethodServer.ServerFlag) {
                
                // Added by Luo Jiao for debug on 20170413 begin
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug(">>>>> DBOperationUtil.commonQueryAll(): sql=" + sql);
                }
                // Added by Luo Jiao end
                
                wtconnection = (WTConnection) methodcontext.getConnection();
                pstmt = wtconnection.prepareStatement(sql);
                if (parameters != null) {
                    for (int j = 1; j <= parameters.length; j++) {
                        pstmt.setString(j, parameters[j - 1]);
                    }
                }

                rs = pstmt.executeQuery();

                ResultSetMetaData metadata = rs.getMetaData();
                int columnCount = metadata.getColumnCount();
                String value = "";
                while (rs.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        value = rs.getString(metadata.getColumnName(i));
                        list.add(value);
                    }
                }
            } else {
                ReflectionUtil.invokeOnMethodServer("commonQueryAll", DBOperationUtil.class, null,
                        new Class[] { sql.getClass(), parameters.getClass() }, new Object[] { sql, parameters });
            }

        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } catch (Exception e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                LOGGER.error(e.getLocalizedMessage(), e);
            }

        }

        return list;
    }

    /**
     * query from database for one column return.
     * 
     * @param sql
     * @param parameters
     * @return
     * @throws WTException
     */
    public static List<String> commonQueryAll4FirstColumn(String sql, String[] parameters,Connection conn) {
        List<String> list = new ArrayList<String>();

        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
        	
	            if (LOGGER.isDebugEnabled()) {
	                LOGGER.debug(">>>>> DBOperationUtil.commonQueryAll4FirstColumn(): sql=" + sql);
	            }
	            pstmt = conn.prepareStatement(sql);
	            if (parameters != null) {
	                for (int j = 1; j <= parameters.length; j++) {
	                    pstmt.setString(j, parameters[j - 1]);
	                }
	            }
	            rs = pstmt.executeQuery();
	            ResultSetMetaData metadata = rs.getMetaData();
	            String value = "";
	            while (rs.next()) {
	                value = rs.getString(metadata.getColumnName(1));
	                list.add(value);
	            }

        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
        }  finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                LOGGER.error(e.getLocalizedMessage(), e);
            }

        }

        return list;
    }
    
    
    
    public static List<String> commonQueryAll4FirstColumn(String sql, String[] parameters) throws WTException {
        List<String> list = new ArrayList<String>();

        WTConnection wtconnection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            MethodContext methodcontext = getMethodContext();
            if (RemoteMethodServer.ServerFlag) {
                
                // Added by Luo Jiao for debug on 20170413 begin
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug(">>>>> DBOperationUtil.commonQueryAll4FirstColumn(): sql=" + sql);
                }
                // Added by Luo Jiao end
                
                wtconnection = (WTConnection) methodcontext.getConnection();
                pstmt = wtconnection.prepareStatement(sql);
                if (parameters != null) {
                    for (int j = 1; j <= parameters.length; j++) {
                        pstmt.setString(j, parameters[j - 1]);
                    }
                }

                rs = pstmt.executeQuery();

                ResultSetMetaData metadata = rs.getMetaData();
                // int columnCount = metadata.getColumnCount();
                String value = "";
                while (rs.next()) {
                    /*
                     * for (int i = 1; i <= columnCount; i++) {
                     * value = rs.getString(metadata.getColumnName(i));
                     * list.add(value);
                     * }
                     */
                    value = rs.getString(metadata.getColumnName(1));
                    list.add(value);
                }
            } else {
                ReflectionUtil.invokeOnMethodServer("commonQueryAll4FirstColumn", DBOperationUtil.class, null,
                        new Class[] { sql.getClass(), parameters.getClass() }, new Object[] { sql, parameters });
            }

        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } catch (Exception e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                LOGGER.error(e.getLocalizedMessage(), e);
            }

        }

        return list;
    }

    /**
     * @param query
     *            from database
     * @return List<Map> :
     * @param sql
     * @param parameters
     * @return
     * @throws WTException
     */
    public static List<Map<String, String>> commonQueryAllToMap(String sql, String[] parameters) throws WTException {

        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        WTConnection wtconnection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            MethodContext methodcontext = getMethodContext();
            if (RemoteMethodServer.ServerFlag) {
                
                // Added by Luo Jiao for debug on 20170413 begin
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug(">>>>> DBOperationUtil.commonQueryAllToMap(): sql=" + sql);
                }
                // Added by Luo Jiao end
                
                wtconnection = (WTConnection) methodcontext.getConnection();
                pstmt = wtconnection.prepareStatement(sql);
                if (parameters != null) {
                    for (int j = 1; j <= parameters.length; j++) {
                        pstmt.setString(j, parameters[j - 1]);
                    }
                }

                rs = pstmt.executeQuery();

                ResultSetMetaData metadata = rs.getMetaData();
                int columnCount = metadata.getColumnCount();
                String value = "";
                while (rs.next()) {
                    Map<String, String> map = new HashMap<String, String>();
                    for (int i = 1; i <= columnCount; i++) {
                        value = rs.getString(metadata.getColumnName(i));
                        map.put(metadata.getColumnName(i), value);
                    }
                    list.add(map);
                }
            } else {
                ReflectionUtil.invokeOnMethodServer("commonQueryAllToMap", DBOperationUtil.class, null, new Class[] {
                        sql.getClass(), parameters.getClass() }, new Object[] { sql, parameters });
            }

        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } catch (Exception e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                LOGGER.error(e.getLocalizedMessage(), e);
            }

        }

        return list;
    }
    
    
    /**
     * @param query
     *            from database
     * @return List<Map> :
     * @param sql
     * @param parameters
     * @return
     * @throws WTException
     */
    public static Map<String, String> commonQueryAllToMap2(String sql, String[] parameters) throws WTException {

        WTConnection wtconnection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map<String, String> map = new HashMap<String, String>();
        try {
            MethodContext methodcontext = getMethodContext();
            if (RemoteMethodServer.ServerFlag) {
            	
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug(">>>>> DBOperationUtil.commonQueryAllToMap2(): sql=" + sql);
                }
                
                wtconnection = (WTConnection) methodcontext.getConnection();
                
                pstmt = wtconnection.prepareStatement(sql);
                if (parameters != null) {
                    for (int j = 1; j <= parameters.length; j++) {
                        pstmt.setString(j, parameters[j - 1]);
                    }
                }
                
                rs = pstmt.executeQuery();
                ResultSetMetaData metadata = rs.getMetaData();
                String tempValue = null;
                String tempKey = null;
                while (rs.next()) {
                    tempKey = rs.getString(metadata.getColumnName(1));
                    tempValue = rs.getString(metadata.getColumnName(2));
                    if(tempKey!=null && tempKey.length()>0 && tempValue!=null && tempValue.length()>0 ){
                        map.put(tempKey, tempValue);
                    }
                }
            } else {
                ReflectionUtil.invokeOnMethodServer("commonQueryAllToMap2", DBOperationUtil.class, null, new Class[] {
                        sql.getClass(),parameters.getClass()}, new Object[] { sql,parameters});
            }

        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } catch (Exception e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                LOGGER.error(e.getLocalizedMessage(), e);
            }

        }

        return map;
    }

    private static MethodContext getMethodContext() throws WTException {

        MethodContext methodcontext = null;
        methodcontext = MethodContext.getContext();

        return methodcontext;
    }

    /**
     * execute non-query sql script ,such as update/insert
     * 
     * @param sql
     * @throws WTException
     */
    public static void executeNonQuery(String sql) throws WTException {
        
        // Added by Luo Jiao for debug on 20170413 begin
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(">>>>> DBOperationUtil.executeNonQuery(String): sql=" + sql);
        }
        // Added by Luo Jiao end

        WTConnection conn = getWTConnection();
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate(sql);
            conn.getConnection().commit();
        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            free(null, stmt);
        }
    }

    /**
     * execute non-query sql,such as insert/update,can input multiple parameters
     * 
     * @param sql
     * @param objects
     * @throws WTException
     */
    public static void executeNonQuery(String sql, Object... objects) throws WTException {
        
        // Added by Luo Jiao for debug on 20170413 begin
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(">>>>> DBOperationUtil.executeNonQuery(String, Object...): sql=" + sql);
        }
        // Added by Luo Jiao end

        WTConnection conn = getWTConnection();
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                stmt.setObject(i + 1, objects[i]);
            }
            stmt.executeUpdate();
            conn.getConnection().commit();
        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            free(null, stmt);
        }
    }
    
    /**
     * execute non-query sql,such as insert/update,can input multiple parameters
     * 
     * @param sql
     * @param objects
     * @throws WTException
     */
    public static void executeNonQuery1(String sql, Connection conn, Object[] objects) throws WTException {
        
        // Added by Luo Jiao for debug on 20170413 begin
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(">>>>> DBOperationUtil.executeNonQuery1(): sql=" + sql);
        }
        // Added by Luo Jiao end

        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                stmt.setObject(i + 1, objects[i]);
            }
            stmt.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            free(null, stmt);
        }
    }

    /**
     * execute batch non-query sql script
     * 
     * @param sql
     * @param pList
     * @throws WTException
     */
    public static void executeNonQueryBatch(String sql, List<Object[]> pList) throws WTException {
        
        // Added by Luo Jiao for debug on 20170413 begin
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(">>>>> DBOperationUtil.executeNonQueryBatch(String, List<Object[]>): sql=" + sql);
        }
        // Added by Luo Jiao end

        WTConnection conn = getWTConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            for (Object[] parameters : pList) {
                if (parameters != null) {
                    for (int j = 1; j <= parameters.length; j++) {
                        stmt.setObject(j, parameters[j - 1]);
                    }
                    stmt.addBatch();
                }
            }
            stmt.executeBatch();
            conn.getConnection().commit();
        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            free(null, stmt);
        }
    }

    /**
     * execute batch non-query sql script
     * 
     * @param sql
     * @param pList
     * @throws WTException
     */
    public static void executeNonQueryBatch(String sql, List<Object[]> pList, Connection connection) throws WTException {
        
        // Added by Luo Jiao for debug on 20170413 begin
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(">>>>> DBOperationUtil.executeNonQueryBatch(String, List<Object[]>, Connection): sql=" + sql);
        }
        // Added by Luo Jiao end

        PreparedStatement stmt = null;
        try {
            stmt = connection.prepareStatement(sql);
            for (Object[] parameters : pList) {
                if (parameters != null) {
                    for (int j = 1; j <= parameters.length; j++) {
                        stmt.setObject(j, parameters[j - 1]);
                    }
                    stmt.addBatch();
                }
            }
            stmt.executeBatch();
            connection.commit();
        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        } finally {
            free(null, stmt);
        }
    }

    /**
     * free all resource ,must invoke this method when execute query
     * 
     * @param rs
     * @param st
     */
    public static void free(ResultSet rs, Statement st) throws WTException {

        // free ResultSet
        free(rs);
        // free Statement
        free(st);
    }

    /**
     * free ResultSet
     * 
     * @param rs
     */
    private static void free(ResultSet rs) throws WTException {

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        }

    }

    /**
     * free Statement
     * 
     * @param st
     */
    private static void free(Statement st) throws WTException {

        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        }
    }

    /**
     * Executes a batch of commands that is composed by the precompiled SQL
     * statement and the parameters.
     * 
     * @param sql
     *            A precompiled SQL Data Manipulation Language (DML) statement,
     *            such as INSERT, UPDATE or DELETE; or an SQL statement that
     *            returns nothing, such as a DDL statement.
     * @param paraListCollection
     *            A List containing a lot of parameter groups, each parameter
     *            group can work independently for the precompiled SQL
     *            statement. The class of parameter can be Long or String.
     * @return An array of update counts containing one element for each command
     *         in the batch. The elements of the array are ordered according to
     *         the order in which commands were added to the batch.
     * @throws WTException
     *             If a database access error occurs, this method is called on a
     *             closed Statement or the given SQL statement produces a
     *             ResultSet object.
     */

    /**
     * commit
     * 
     * @param conn
     * @throws WTException
     */
    public static void commit(WTConnection conn) throws WTException {
        try {
            conn.getConnection().commit();
        } catch (SQLException e) {
            LOGGER.error(e.getLocalizedMessage(), e);
            throw new WTException(e);
        }
    }


    /**
     * update DB.Connection is not close, need close connection out of the method.
     * 
     * @param sql
     * @param connection
     * @throws WTException
     * @throws SQLException
     */
    public static void updateDB(String sql, Connection connection) throws WTException, SQLException {
        
        // Added by Luo Jiao for debug on 20170413 begin
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(">>>>> DBOperationUtil.updateDB(): sql=" + sql);
        }
        // Added by Luo Jiao end
        
        // Connection connection = null;
        Statement statement = null;
        try {
            if (connection != null) {
                connection.setAutoCommit(false);
                statement = connection.createStatement();
                statement.execute(sql);
                connection.commit();
            }
        } catch (Exception e) {
            // LOGGER.error("Error occured when update BD.sql=>"+sql);
            if (connection != null) {
                connection.rollback();
            }
            throw new WTException(e, sql);
        } finally {
            if (statement != null) {
                statement.close();
            }
        }

    }

    /**
     * Check data whether exist in DB
     * 
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     * @throws WTException
     */
    public static boolean checkExistInDB(String sql, String[] parameters, Connection connection) throws SQLException,
            WTException {

        // Added by Luo Jiao for debug on 20170413 begin
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(">>>>> DBOperationUtil.checkExistInDB(): sql=" + sql);
        }
        // Added by Luo Jiao end
        
        PreparedStatement statement = null;
        try {
            // connection.setAutoCommit(false);
            statement = connection.prepareStatement(sql);

            if (parameters != null) {
                for (int j = 1; j <= parameters.length; j++) {
                    statement.setString(j, parameters[j - 1]);
                }
            }
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return true;
            }
        } catch (Exception e) {
            LOGGER.error("Query DB failed.", e);
            throw new WTException(e);

        } finally {
            /*
             * if (connection != null) {
             * connection.close();
             * }
             */
            if (statement != null) {
                statement.close();
            }
        }
        return false;
    }

    /**
     * Query data from DB.Connection is not close, need close connection out of the method.
     * 
     * @param sql
     * @param parameters
     * @param connection
     * @return List<Map<String, String>>
     * @throws SQLException
     * @throws WTException
     */
    public static List<Map<String, String>> queryData(String sql, String[] parameters, Connection connection)
            throws SQLException, WTException {
        
        // Added by Luo Jiao for debug on 20170413 begin
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(">>>>> DBOperationUtil.queryData(): sql=" + sql);
        }
        // Added by Luo Jiao end
        
        List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            if (parameters != null) {
                for (int j = 1; j <= parameters.length; j++) {
                    statement.setString(j, parameters[j - 1]);
                }
            }
            ResultSet resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                Map<String, String> resultMap = new HashMap<String, String>();
                String value = "";
                for (int i = 1; i <= columnCount; i++) {
                    value = resultSet.getString(metaData.getColumnName(i));
                    resultMap.put(metaData.getColumnName(i), value);
                }
                resultList.add(resultMap);
            }
        } catch (Exception e) {
            LOGGER.error("Query DB failed.", e);
            throw new WTException(e);

        } finally {
            if (statement != null) {
                statement.close();
            }
        }
        return resultList;
    }
    
    

    /**
     * Close DB connection.
     * 
     * @param connection
     */
    public static void closeDBConnection(Connection connection) {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                // connection = null;
            }
        } catch (SQLException e) {
            LOGGER.error("close sql server connection error:", e);
        }
    }

    /**
     * Get sql server connection from connection pool.
     * 
     * @return Connection
     * @throws WTException
     */

}
